﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common;
using Northwind.Domain;

namespace Ex02
{
    class Program
    {
        static void Main(string[] args)
        {
            // The following query returns all purchases with a null CustomerID:
            var db = new NorthwindEntities();
            //int? i = null;
            db.Purchases.Where(p => p.CustomerID == null)
                .Dump("Customers with null CustomerID, using a null literal");

            // Look at the SQL translatation - the predicate reads "WHERE [t0].[CustomerID] IS NULL".
            // The "IS" keyword in SQL is essential here, to avoid running afoul of SQL's three-value null logic.
            // If it translated instead to "WHERE [t0].[CustomerID] = NULL" we would get no rows back.

            // But suppose that you want to use a variable instead of a literal, in other words:

            int? customerID = null;
            db.Purchases.Where(p => p.CustomerID == customerID) 
                //SQL:WHERE ([Extent1].[CustomerID] = @p__linq__0) OR (([Extent1].[CustomerID] IS NULL) AND (@p__linq__0 IS NULL))
                .Dump("Customers with a null CustomerID, using a variable");

            // Notice, now, that no results are returned. We can see why by looking at the SQL: the
            // the predicate translates to: "WHERE [t0].[CustomerID] = @p0".

            // Fortunately, there's an easy workaround. If you use the static object.Equals method to
            // perform the equality comparison, LINQ to SQL emits an "IS NULL" expression if the value
            // is null, otherwise it emits an "= @p" expression:

            db.Purchases.Where(p => object.Equals(p.CustomerID, customerID))
                //SQL: WHERE [Extent1].[CustomerID] IS NULL
                .Dump("Using the static object.Equals method, with a null variable");

            customerID = 1;
            db.Purchases.Where(p => object.Equals(p.CustomerID, customerID)) //扔出异常
                .Dump("Using the static object.Equals method, with a non-null variable");

            Console.ReadKey();
        }
    }
}
